Option Explicit On
Public nfres As Integer

Private Sub btnAgregar_Click()
    agregar()
End Sub

Private Sub btnEliminar_Click()
    eliminar()
End Sub
Sub agregar()
    If lbCampos.ListCount <> 0 And lbCampos.ListIndex <> -1 Then
        Dim s As String
        s = lbCampos.List(lbCampos.ListIndex)
        lbUnicos.AddItem(s)
        lbCampos.RemoveItem(lbCampos.ListIndex)
    End If
    If lbCampos.ListCount = 0 Then btnAgregar.Enabled = False
    btnEliminar.Enabled = True
    btnDeDuplicar.Enabled = True
End Sub

Sub eliminar()
    If lbUnicos.ListCount <> 0 And lbUnicos.ListIndex <> -1 Then
        Dim s As String
        s = lbUnicos.List(lbUnicos.ListIndex)
        lbCampos.AddItem(s)
        lbUnicos.RemoveItem(lbUnicos.ListIndex)
    End If
    If lbUnicos.ListCount = 0 Then
        btnEliminar.Enabled = False
        btnDeDuplicar.Enabled = False
    End If
    btnAgregar.Enabled = True
End Sub

Private Sub btnDeDuplicar_Click()
    Dim hojades As String
    hojades = tbHojaDes.Text
    Dim cnhojades As String
    cnhojades = ""
    Dim existe As Boolean
    existe = False
    Dim h As Worksheet
    For Each h In Worksheets
        If h.Name = tbHojaDes.Text Then
            existe = True
        End If
    Next
    If existe Then
        If MsgBox("Hoja " & hojades & " ya existe. Desea sustituir su contenido ? ", vbYesNo + vbExclamation, "Sustituir contenido " & hojades) = vbYes Then
            Worksheets(hojades).Select()
            Worksheets(hojades).Range("A1").Select()
            Worksheets(hojades).UsedRange.Select()
            Worksheets(hojades).UsedRange.Delete()
            Worksheets(hojades).Range("A1").Select()
        Else
            MsgBox("No se realiza ninguna 'Deduplicacin'.")
            Exit Sub
        End If
    Else
        Dim hay As Integer
        hay = Worksheets.Count
        Dim nueva As Worksheet
        nueva = Worksheets.Add(after:=Worksheets(hay), Type:=xlWorksheet)
        Worksheets(nueva.Index).Name = tbHojaDes.Text
    End If
    deduplicar()
End Sub

Private Sub btnSalir_Click()
    Unload(Me)
End Sub

Private Sub cborigen_Change()
    inicializar()
End Sub

Private Sub chCab_Click()
    If tbHojaDes.Text <> "" Then
        inicializar()
    End If
End Sub

Private Sub lbCampos_Click()
    If lbCampos.ListCount > 0 Then btnAgregar.Enabled = True
End Sub

Private Sub lbCampos_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    agregar()
End Sub

Private Sub lbUnicos_Click()
    If lbUnicos.ListCount > 0 Then btnEliminar.Enabled = True
End Sub

Private Sub lbUnicos_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    eliminar()
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
    chCab.Value = True
    carga_origen()
    inicializar()
End Sub

Sub inicializar()
    Worksheets(cborigen.Text).Activate()
    fabrica_lista()
    btnDeDuplicar.Default = True
    tbHojaDes.Text = "Resultado"
    btnAgregar.Enabled = False
    btnEliminar.Enabled = False
    If lbUnicos.ListCount = 0 Then btnDeDuplicar.Enabled = False
    lbNfilas.Caption = numFilas & " filas"
    Worksheets(cborigen.Text).Activate()
    nfres = 0
End Sub

Sub carga_origen()
    Dim h As Worksheet
    For Each h In Worksheets
        If h.Name <> "Resultado" Then
            cborigen.AddItem(h.Name)
        End If
    Next
    If cborigen.ListCount > 0 Then
        cborigen.ListIndex = 0
    End If
End Sub

Sub deduplicar()
    nfres = 0
    Dim h, r As Worksheet
    h = Worksheets(cborigen.Text)
    r = Worksheets(tbHojaDes.Text)
    r.Select()
    r.UsedRange.Delete()
    Dim nfila, ncolumna, nfilas, ncolumnas, filaini As Integer
    h.Select()
    If chCab.Value = True Then
        filaini = 2
        agregar_resultado(h.Rows(1))
    Else
        filaini = 1
    End If
    nfilas = numFilas
    ncolumnas = numColumnas
    For nfila = filaini To nfilas
        ' columnas a tener en cuenta
        Dim cs() As String
        ReDim cs(1 To lbUnicos.ListCount)
        For ncolumna = 1 To lbUnicos.ListCount
            Dim colCmp As String
            Dim partes As Object
            partes = Split(lbUnicos.List(ncolumna - 1))
            colCmp = partes(0)
            cs(ncolumna) = Cells(nfila, CInt(colCmp))
        Next ncolumna
        If Not (busca_En_Resultado(cs)) Then
            agregar_resultado(h.Rows(nfila))
        End If
    Next nfila
    r.Select()
End Sub

Sub agregar_resultado(ByVal r As Range)
    Dim h As Worksheet
    h = Worksheets(tbHojaDes.Text)
    Dim nf As Integer
    nfres = nfres + 1
    Dim ncolumna As Integer
    For ncolumna = 1 To numColumnas
        h.Cells(nfres, ncolumna) = r.Cells(1, ncolumna)
    Next ncolumna
End Sub

Function busca_En_Resultado(ByVal cs As Object) As Boolean
    Dim r As Worksheet
    r = Worksheets(tbHojaDes.Text)
    Dim n, nf, nfs, ncolumna, filaini As Integer
    nfs = nfres
    n = UBound(cs)
    Dim existe As Boolean
    existe = False
    If chCab.Value = True Then
        filaini = 2
    Else
        filaini = 1
    End If
    For nf = filaini To nfs
        Dim igual As Boolean
        igual = True
        For ncolumna = 1 To n
            Dim colCmp As String
            Dim partes As Object
            partes = Split(lbUnicos.List(ncolumna - 1))
            colCmp = partes(0)
            Dim compara, contenido As String
            compara = cs(ncolumna)
            contenido = r.Cells(nf, CInt(colCmp))
            If compara <> contenido Then igual = False
        Next ncolumna
        If igual = True Then
            busca_En_Resultado = True
            Exit Function
        End If
    Next nf
    busca_En_Resultado = existe
End Function

Function numFilas() As Integer
    numFilas = Worksheets(cborigen.Text).UsedRange.Cells.Rows.Count
End Function

Function numColumnas() As Integer
    numColumnas = Worksheets(cborigen.Text).UsedRange.Cells.Columns.Count
End Function

Sub fabrica_lista()
    Dim l As Object
    Dim inicio As Boolean
    inicio = True
    lbCampos.Clear()
    lbUnicos.Clear()
    Dim r As Range
    r = Worksheets(cborigen.Text).UsedRange.Rows(1)
    Dim c As Range
    For Each c In r.Columns
        Dim n As String
        If chCab.Value = True Then
            n = c.Value
            If n = "" Then
                n = c.Cells.Column & " - <???>"
            Else
                n = c.Cells.Column & " - " & c.Value
            End If
        Else
            n = c.Cells.Column & " - C" & c.Cells.Column
        End If
        If inicio Then
            lbUnicos.AddItem(n)
            inicio = False
        Else
            lbCampos.AddItem(n)
        End If
    Next
End Sub
